using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace CocaCola_Inventory_System.Data_Access
{
    class detailDB
    {
        public static DataTable getDetails(OdbcConnection con)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;

                com.CommandText = "SELECT * FROM detail_types;";
                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcDataAdapter dAdapt = new OdbcDataAdapter(com);
                DataTable result = new DataTable();
                dAdapt.Fill(result);

                return result;
            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return null;
            }
        }

        public static bool InsertDetail(OdbcConnection con, string name, string description)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;

                com.CommandText = string.Format("SELECT * FROM detail_types WHERE lower(Name)=lower('{0}');", name);
                if (con.State != ConnectionState.Open)
                    con.Open();

                if (com.ExecuteNonQuery() != 0)
                {
                    throw new Exception("Name must be unique");
                    return false;
                }

                com.CommandText = string.Format("INSERT INTO detail_types (Name,Description) VALUES ('{0}','{1}');", name, description);
                if (con.State != ConnectionState.Open)
                    con.Open();

                com.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return false;
            }
        }

        public static bool updateDetails(OdbcConnection con, DataTable input)
        {
            try
            {
                if (con == null)
                    throw new Exception("Null Connetion");
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;

                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcDataAdapter dAdapt = new OdbcDataAdapter();
                dAdapt.UpdateCommand = new OdbcCommand("UPDATE detail_types SET Id=?,Name=?,Description=? WHERE Id=?;", con);
                dAdapt.DeleteCommand = new OdbcCommand("DELETE FROM detail_types WHERE Id=?;", con);

                dAdapt.UpdateCommand.Parameters.Add("@Id", OdbcType.Int, sizeof(Int32), "Id");
                dAdapt.UpdateCommand.Parameters.Add("@Name", OdbcType.VarChar, 45, "Name");
                dAdapt.UpdateCommand.Parameters.Add("@Description", OdbcType.VarChar, 45, "Description");
                dAdapt.UpdateCommand.Parameters.Add("@oldId", OdbcType.Int, sizeof(Int32), "Id").SourceVersion = DataRowVersion.Original;

                dAdapt.DeleteCommand.Parameters.Add("@Id", OdbcType.Int, sizeof(Int32), "Id").SourceVersion = DataRowVersion.Original;

                dAdapt.Update(input);

                dAdapt.DeleteCommand = new OdbcCommand("DELETE FROM details WHERE Detail_Type_Id=?;", con);
                dAdapt.DeleteCommand.Parameters.Add("@Detail_Type_Id", OdbcType.Int, sizeof(Int32), "Id");

                dAdapt.Update(input);

                return true;
            }
            catch (FormatException ex)
            {
                Common.ReportError(ex);
                return false;
            }
        }
    }
}
